package gre.db;

import java.io.*;
import java.sql.*;
import java.util.*;

/**
 * 
 * @author nacuong
 */
public class DBCreator {

  static final String dbName = "gre.db";
  
  /**
   * Create database GRE from the connection, drop on exist
   * @param connection
   */
  public static void createDatabase(Statement statement) throws IOException, SQLException {
    /* drop table on exists */
    dropTable(statement);
    /* create table */
    createTable(statement);
    /* insert data into table */
    DBInserter.insertSample(statement);
  }

  /**
   * Drop words and sentences table in database
   * @param statement
   * @throws SQLException
   */
  public static void dropTable(Statement statement) throws SQLException {
    statement.executeUpdate("Drop Table If Exists words");
    statement.executeUpdate("Drop Table If Exists sentences");
  }

  /**
   * Create words and setences table in database
   * @param connection
   */
  public static void createTable(Statement statement) {
    try {
      statement.executeUpdate(
              "Create Table words"
              + "("
              + "wid INTEGER PRIMARY KEY, "
              + "word TEXT NOT NULL, "
              + "meaning TEXT NOT NULL"
              + ")");
      statement.executeUpdate(
              "Create Table sentences"
              + "("
              + "wid INTEGER NOT NULL,"
              + "sentence TEXT NOT NULL"
              + ")");
    } catch (SQLException e) {
      System.out.println("SQL Exception: " + e.toString());
    }
  }

  /**
   * Get connection from the properties specified in the file database.properties
   * @return the database connection
   * @throws SQLException
   * @throws IOException
   */
  public static Connection getConnection() {
    try {
      System.setProperty("jdbc.drivers","org.sqlite.JDBC");
      return DriverManager.getConnection("jdbc:sqlite:" + DBHelper.getCurrentPath() + dbName);
    } catch (SQLException e) {
      System.out.println("SQL Exception: " + e.toString());
      return null;
    }
  }
}
